﻿
namespace HsTec.Mobile.Green.SqlServerDAL
{
    using System;
    using System.Collections;
    using System.Text;
    using System.Data;
    using System.Data.SqlClient;
    using Baimasoft.FrameWork.Base;
    using Baimasoft.Manage.SqlServerDAL;

    public class Study
    {
        private static string SQL_SELECT_Type_DATAFILEINFO = @"SELECT Type,Name FROM StudyType {0} Order by Dorder ";

        //取页面显示信息
        private static string SQL_SELECT_ALL_DATAFILEINFO = @" SELECT a.id, b.name AS TypeName, a.title, a.writer, a.writerFrom, 
                                                                      CASE WHEN isshow = '1' THEN '是' ELSE '否' END IsShowName, CreatDt,
                                                                      CASE WHEN ISAuditing = '1' THEN '是' ELSE '否' END ISAuditingName
                                                                FROM Study a LEFT OUTER JOIN
                                                                      StudyType b ON a.TypeID = b.Type {0} ";

        private static string SQL_SELECT_ALL_INFO = @" SELECT a.id,a.TypeID, b.name AS TypeName, a.title, a.writer, a.writerFrom, 
                                                                      CASE WHEN isshow = '1' THEN '是' ELSE '否' END IsShowName, CreatDt,
                                                                      CASE WHEN ISAuditing = '1' THEN '是' ELSE '否' END ISAuditingName,
                                                                      a.Content,a.KeyWords,a.Dorder,a.IsShow,a.ISAuditing,a.Assessor,a.AssessorDT,a.AssessorMome
                                                                FROM Study a LEFT OUTER JOIN
                                                                      StudyType b ON a.TypeID = b.Type {0} ";

        public Study()
        {
        }

        /// <summary>
        /// 类别信息
        /// </summary>
        /// <param name="queryStr"></param>
        /// <returns></returns>
        public System.Data.DataTable GetTypeInfos(string queryStr)
        {
            string sql = String.Format(SQL_SELECT_Type_DATAFILEINFO, queryStr != null && queryStr.Trim().Length > 0 ? " WHERE " + queryStr : "");
            DataTable dt = SqlHelper.GetDataTable(AppConfig.ConnectionString, CommandType.Text, sql, null);
            return dt;
        }

        /// <summary>
        /// 管理页面信息
        /// </summary>
        /// <param name="queryStr"></param>
        /// <returns></returns>
        public System.Data.DataTable GetManageInfos(string queryStr)
        {
            string sql = String.Format(SQL_SELECT_ALL_DATAFILEINFO, queryStr != null && queryStr.Trim().Length > 0 ? " WHERE " + queryStr : "");
            DataTable dt = SqlHelper.GetDataTable(AppConfig.ConnectionString, CommandType.Text, sql, null);
            return dt;
        }

        /// <summary>
        /// 管理页面信息
        /// </summary>
        /// <param name="queryStr"></param>
        /// <returns></returns>
        public System.Data.DataTable GetInfos(string queryStr)
        {
            string sql = String.Format(SQL_SELECT_ALL_INFO, queryStr != null && queryStr.Trim().Length > 0 ? " WHERE " + queryStr : "");
            DataTable dt = SqlHelper.GetDataTable(AppConfig.ConnectionString, CommandType.Text, sql, null);
            return dt;
        }

        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="IDs"></param>
        /// <returns></returns>
        public int DeleteInfos(string IDs)
        {
            try
            {
                string sql = " delete Study  WHERE (ID IN (" + IDs + ")) ";
                int iCount = SqlHelper.ExecuteNonQuery(AppConfig.ConnectionString, CommandType.Text, sql, null);
                return iCount;

            }
            catch (System.Exception ex)
            {
                return 0;
            }
        }


        public int InsertInfo(string TypeID, string Title, string Content, string keyWords, string Writer, string IsShow, DateTime CreatDt)
        {

            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into Study(");
                strSql.Append("TypeID,Title,Content,keyWords,Writer,IsShow,CreatDt)");
                strSql.Append(" values (");
                strSql.Append("@TypeID,@Title,@Content,@keyWords,@Writer,@IsShow,@CreatDt)");
                SqlParameter[] parameters = {
					new SqlParameter("@TypeID", SqlDbType.Int,4),
					new SqlParameter("@Title", SqlDbType.VarChar,100),
					new SqlParameter("@Content", SqlDbType.Text),
					new SqlParameter("@keyWords", SqlDbType.VarChar,200),
					new SqlParameter("@Writer", SqlDbType.VarChar,50),
					new SqlParameter("@IsShow", SqlDbType.Char,1),
					new SqlParameter("@CreatDt", SqlDbType.DateTime)};
                parameters[0].Value = TypeID;
                parameters[1].Value = Title;
                parameters[2].Value = Content;
                parameters[3].Value = keyWords;
                parameters[4].Value = Writer;
                parameters[5].Value = IsShow;
                parameters[6].Value = CreatDt;

                int iCount = SqlHelper.ExecuteNonQuery(AppConfig.ConnectionString, CommandType.Text, strSql.ToString(), parameters);
                return iCount;
            }
            catch (System.Exception ex)
            {
                //LogDAL.Log("新闻大类",ex.Message,"插入异常",ex.StackTrace.ToString());
                throw ex;
            }
        }

        public int UpdateInfo(string TypeID, string Title, string Content, string keyWords, string Writer, string IsShow, DateTime CreatDt, string ID)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Study set ");
                strSql.Append("TypeID=@TypeID,");
                strSql.Append("Title=@Title,");
                strSql.Append("Content=@Content,");
                strSql.Append("keyWords=@keyWords,");
                strSql.Append("Writer=@Writer,");
                strSql.Append("IsShow=@IsShow,");
                strSql.Append("CreatDt=@CreatDt");
                strSql.Append(" where ID=@ID");
                SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@TypeID", SqlDbType.Int,4),
					new SqlParameter("@Title", SqlDbType.VarChar,100),
					new SqlParameter("@Content", SqlDbType.Text),
					new SqlParameter("@keyWords", SqlDbType.VarChar,200),
					new SqlParameter("@Writer", SqlDbType.VarChar,50),
					new SqlParameter("@IsShow", SqlDbType.Char,1),
					new SqlParameter("@CreatDt", SqlDbType.DateTime)};

                parameters[0].Value = ID;
                parameters[1].Value = TypeID;
                parameters[2].Value = Title;
                parameters[3].Value = Content;
                parameters[4].Value = keyWords;
                parameters[5].Value = Writer;
                parameters[6].Value = IsShow;
                parameters[7].Value = CreatDt;

                int iCount = SqlHelper.ExecuteNonQuery(AppConfig.ConnectionString, CommandType.Text, strSql.ToString(), parameters);
                return iCount;
            }
            catch (System.Exception ex)
            {
                //LogDAL.Log("新闻大类",ex.Message,"更新异常",ex.StackTrace.ToString());
                throw ex;
            }
        }

        /// <summary>
        /// 审核内容
        /// </summary>
        /// <param name="ISAuditing"></param>
        /// <param name="AssessorMome"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        public int AssessorInfo(string ISAuditing,string Assessor, string AssessorMome,string ID)
        {
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Study set ");
                strSql.Append("ISAuditing=@ISAuditing,");
                strSql.Append("AssessorMome=@AssessorMome,");
                strSql.Append("AssessorDT=@AssessorDT,");
                strSql.Append("Assessor=@Assessor");
                strSql.Append(" where ID=@ID");

                SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@ISAuditing", SqlDbType.VarChar,50),
					new SqlParameter("@AssessorMome", SqlDbType.Text),
					new SqlParameter("@AssessorDT", SqlDbType.DateTime),
					new SqlParameter("@Assessor", SqlDbType.VarChar,50)};

                parameters[0].Value = ID;
                parameters[1].Value = ISAuditing;
                parameters[2].Value = AssessorMome;
                parameters[3].Value = DateTime.Now;
                parameters[4].Value = Assessor;

                int iCount = SqlHelper.ExecuteNonQuery(AppConfig.ConnectionString, CommandType.Text, strSql.ToString(), parameters);
                return iCount;
            }
            catch (System.Exception ex)
            {
                //LogDAL.Log("新闻大类",ex.Message,"更新异常",ex.StackTrace.ToString());
                throw ex;
            }
        }

    }
}
